package com.autoMobile.dao;

import com.autoMobile.bean.rrmBx;
import com.autoMobile.bean.rrmSgjl;
import com.autoMobile.util.DBUtil;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class rrmbxDao {

    // 查找用户信息
    public List<rrmBx> listBx() {
        List<rrmBx> list = new ArrayList<>();
        String sql = "SELECT * FROM bx";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql);
             ResultSet rs = pst.executeQuery()) {

            while (rs.next()) {
                rrmBx bx = new rrmBx(
                    rs.getInt(1),
                    rs.getString(2),
                    rs.getString(3),
                    rs.getDouble(4),
                    rs.getString(5),
                    rs.getString(6),
                    rs.getString(7),
                    rs.getString(8),
                    rs.getString(9),
                    rs.getString(10),
                    rs.getString(11)
                );
                list.add(bx);
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 在实际应用中，应记录日志或抛出自定义异常
        }
        return list;
    }
    // 查找车牌信息
    public List<rrmBx> listCph() {
        List<rrmBx> list = new ArrayList<>();
        String sql = "SELECT cph FROM bx";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql);
             ResultSet rs = pst.executeQuery()) {

            while (rs.next()) {
                rrmBx bx = new rrmBx(

                    rs.getString(1)

                );
                list.add(bx);
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 在实际应用中，应记录日志或抛出自定义异常
        }
        return list;
    }
    // 增加用户
    public int addBx(rrmBx bx) {
        rrmBx existingBx = findUserById(bx.getId());

        if (existingBx != null) {
            return 1; // 如果已存在，返回1表示添加失败
        }

        String sql = "INSERT INTO bx(bdh, bxzl, be, bxgs, csrq, jzrq, cph, cx, ys, cjh) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql)) {
            
            pst.setString(1, bx.getBdh());
            pst.setString(2, bx.getBxzl());
            pst.setDouble(3, bx.getBe());
            pst.setString(4, bx.getBxgs());
            pst.setString(5, bx.getCsrq());
            pst.setString(6, bx.getJzrq());
            pst.setString(7, bx.getCph());
            pst.setString(8, bx.getCx());
            pst.setString(9, bx.getYs());
            pst.setString(10, bx.getCjh());
            
            int rowsAffected = pst.executeUpdate();
            return rowsAffected > 0 ? 0 : -1; // 0表示成功，-1表示失败
        } catch (SQLException e) {
            e.printStackTrace(); // 可以记录到日志
            return -1; // 数据库操作失败
        }
    }

    // Servlet的doPost方法
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");

        // 获取参数
        String bdh = request.getParameter("bdh");
        String bxzl = request.getParameter("bxzl");
        String beStr = request.getParameter("be");
        String bxgs = request.getParameter("bxgs");
        String csrq = request.getParameter("csrq");
        String jzrq = request.getParameter("jzrq");
        String cph = request.getParameter("cph");
        String cx = request.getParameter("cx");
        String ys = request.getParameter("ys");
        String cjh = request.getParameter("cjh");

        // 转换保额
        double be = 0.0;
        try {
            if (beStr != null && !beStr.isEmpty()) {
                try {
                    be = Double.parseDouble(beStr);
                } catch (NumberFormatException e) {
                    request.setAttribute("message", "保额格式错误");
                    request.getRequestDispatcher("user/addBx.jsp").forward(request, response);
                    return;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            request.setAttribute("message", "数据处理失败，请重试");
            request.getRequestDispatcher("user/addBx.jsp").forward(request, response);
            return;
        }
        
        // 构造 Bx 对象
        rrmBx bx = new rrmBx(bdh, bxzl, be, bxgs, csrq, jzrq, cph, cx, ys, cjh);

        // 操作数据库
        rrmbxDao dao = new rrmbxDao();
        int result = dao.addBx(bx);
        
        if (result == 1) {
            request.setAttribute("message", "保单号已经存在");
            request.getRequestDispatcher("user/addBx.jsp").forward(request, response);
        } else if (result == 0) {
            response.sendRedirect("BxServlet");
        } else {
            request.setAttribute("message", "系统错误，请重试");
            request.getRequestDispatcher("user/addBx.jsp").forward(request, response);
        }
    }
    

    // 检查 bdh 是否存在
    public rrmBx findUserById(int id) {
        String sql = "SELECT * FROM bx WHERE id=?";
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql)) {

            pst.setInt(1, id);  // 设置 ID 参数
            
            try (ResultSet rs = pst.executeQuery()) {
                if (rs.next()) {
                    // 使用列名而非索引
                    return new rrmBx(
                        rs.getInt("id"),         // 假设 "id" 是表中的主键字段
                        rs.getString("bxzl"),    // 假设 "bxzl" 是表中的字段
                        rs.getString("be"),
                        rs.getDouble("bxgs"),
                        rs.getString("csrq"),
                        rs.getString("jzrq"),
                        rs.getString("cph"),
                        rs.getString("cx"),
                        rs.getString("ys"),
                        rs.getString("cjh"),
                        rs.getString("bdh")
                    );
                }
            }
        } catch (SQLException e) {
            // 记录更详细的日志
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, 
                "Database error while finding user by id. ID: " + id, e);
        }
        
        return null;  // 没有找到记录
    }
    // 检查 id 是否存在
    public rrmSgjl findSgjlById(int id) {
        String sql = "SELECT * FROM sgjl WHERE id=?";
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql)) {

            pst.setInt(1, id);  // 设置 ID 参数
            
            try (ResultSet rs = pst.executeQuery()) {
                if (rs.next()) {
                    // 使用列名而非索引
                    return new rrmSgjl(
                        rs.getInt("id"),         
                        rs.getString("sglx"),    
                        rs.getString("xxms"),
                        rs.getString("sgsj"),
                        rs.getString("sgdd"),
                        rs.getString("jsy"),
                        rs.getString("cph"),
                        rs.getString("lpje"),
                            rs.getString("zt")
                    );
                }
            }
        } catch (SQLException e) {
            // 记录更详细的日志
            Logger.getLogger(getClass().getName()).log(Level.SEVERE, 
                "Database error while finding user by id. ID: " + id, e);
        }
        
        return null;  // 没有找到记录
    }

 // 删除用户
    public boolean DelBx(int id) {
    		Connection conn=null;
    		PreparedStatement pst=null;
    		 System.out.println("收到指令");
    		String sql="DELETE FROM bx WHERE id=?";
    		try {
    			conn=DBUtil.getConnection();
    			pst=conn.prepareStatement(sql);
    			pst.setInt(1, id);
    			
    			int rowsAffected=pst.executeUpdate();
    			return rowsAffected > 0;
    			
    			
    		}catch(Exception e) {
    			e.printStackTrace();
    			return false;
    		}finally {
    			DBUtil.close(conn, pst, null);
    			
    		}
    	} 
    // 删除事故记录
    public boolean DelSgjl(int id) {
    		Connection conn=null;
    		PreparedStatement pst=null;
    		 System.out.println("收到指令");
    		String sql="DELETE FROM sgjl WHERE id=?";
    		try {
    			conn=DBUtil.getConnection();
    			pst=conn.prepareStatement(sql);
    			pst.setInt(1, id);
    			
    			int rowsAffected=pst.executeUpdate();
    			return rowsAffected > 0;
    			
    			
    		}catch(Exception e) {
    			e.printStackTrace();
    			return false;
    		}finally {
    			DBUtil.close(conn, pst, null);
    			
    		}
    	} 
    public int UpdateBx(rrmBx bx) {
        String sql = "UPDATE bx SET bxzl = ?, be = ?, bxgs = ?, csrq = ?, jzrq = ?, cph = ?, cx = ?, ys = ?, cjh = ?, bdh = ? WHERE id = ?";
        int result = 0;
        
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql)) {

            pst.setString(1, bx.getBxzl());
            pst.setDouble(2, bx.getBe());
            pst.setString(3, bx.getBxgs());
            pst.setString(4, bx.getCsrq());
            pst.setString(5, bx.getJzrq());
            pst.setString(6, bx.getCph());
            pst.setString(7, bx.getCx());
            pst.setString(8, bx.getYs());
            pst.setString(9, bx.getCjh());
            pst.setString(10, bx.getBdh());
            pst.setInt(11, bx.getId());
            
            result = pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();  // 这里可以考虑更换为日志记录
        }
        
        return result;
    }
    
    	

    // 辅助方法：设置日期，如果为null则设置为NULL
    private void setDate(PreparedStatement pst, int parameterIndex, Date date) throws SQLException {
        if (date != null) {
            pst.setTimestamp(parameterIndex, new java.sql.Timestamp(date.getTime()));
        } else {
            pst.setNull(parameterIndex, java.sql.Types.TIMESTAMP);
        }
    }
    
    
    //以下是有关保险记录的代码
    	//增加保险记录    
    public int Addsglj(rrmSgjl sgjl) {
    	int result=0;
    	Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		String sql="insert into sgjl(sglx,xxms,sgsj,sgdd,jsy,cph,lpje,zt) values(?,?,?,?,?,?,?,?)";
		try {
			conn=DBUtil.getConnection();
			pst=conn.prepareStatement(sql);
			System.out.println(sgjl.getSgdd());
			pst.setString(1, sgjl.getSglx());
			pst.setString(2, sgjl.getXxms());
			pst.setString(3, sgjl.getSgsj());
			pst.setString(4, sgjl.getSgdd());
			pst.setString(5, sgjl.getJsy());
			pst.setString(6, sgjl.getCph());
			pst.setString(7, sgjl.getLpje());
            pst.setString(8, sgjl.getZt());
			result=pst.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(conn, pst, rs);
		}
    	
    	return result;
    }
    // 查找事故记录
    public List<rrmSgjl> listSgjl() {
        List<rrmSgjl> list = new ArrayList<>();
        String sql = "SELECT * FROM sgjl";
        Connection conn=null;
        PreparedStatement pst =null;
        ResultSet rs =null;
        try {conn = DBUtil.getConnection();
             pst = conn.prepareStatement(sql);
             rs = pst.executeQuery();

            while (rs.next()) {
                rrmSgjl sgjl = new rrmSgjl(
                    rs.getInt(1),
                    rs.getString(2),
                    rs.getString(3),
                    rs.getString(4),
                    rs.getString(5),
                    rs.getString(6),
                    rs.getString(7),
                    rs.getString(8),
                        rs.getString(9)
                );
                list.add(sgjl);
            }
        } catch (SQLException e) {
            e.printStackTrace(); // 在实际应用中，应记录日志或抛出自定义异常
        }finally {
			DBUtil.close(conn, pst, rs);
		}
        return list;
    }
	
	//修改第二步
	public int UpdateSgjl(rrmSgjl sgjl) {
			int result = 0;
	        Connection conn=null;
	        PreparedStatement pst=null;
	        ResultSet rs=null;
	        String sql = "UPDATE sgjl SET sglx=?,jsy=?,cph=?,sgsj=?,sgdd=?,xxms=?,lpje=? ,zt=?WHERE id = ?";
	        try {
	        	
	        	conn=DBUtil.getConnection();
	        	pst=conn.prepareStatement(sql);
	        	pst.setInt(8, sgjl.getId());
	        	pst.setString(1, sgjl.getSglx());
	        	pst.setString(2, sgjl.getJsy());
	        	pst.setString(3, sgjl.getCph());
	        	pst.setString(4, sgjl.getSgsj());
	        	pst.setString(5, sgjl.getSgdd());
	        	pst.setString(6, sgjl.getXxms());
	        	pst.setString(7, sgjl.getLpje());
                pst.setString(8, sgjl.getZt());

	        	result = pst.executeUpdate();
	        }catch(Exception e) {
	        	e.printStackTrace();
	        }finally {
	        	DBUtil.close(conn, pst, rs);
	        }
	        return result;
	    }
    public int UpdateSgjlZt(rrmSgjl sgjl) {
        int result = 0;
        Connection conn=null;
        PreparedStatement pst=null;
        ResultSet rs=null;
        String sql = "UPDATE sgjl SET zt=? WHERE id = ?";
        try {

            conn=DBUtil.getConnection();
            pst=conn.prepareStatement(sql);
            pst.setInt(2, sgjl.getId());
            pst.setString(1, sgjl.getZt());


            result = pst.executeUpdate();
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(conn, pst, rs);
        }
        return result;
    }
}

